﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Transport
{
    public class XeControl
    {
        transportEntities db;

        public XeControl()
        {
            db = new transportEntities();
        }

        public List<Xe> TimXeTheoSoCho(int socho)
        {
            try
            {
                var result = (from a in db.hang_xe
                              from b in db.thuong_hieu
                              where a.ma_hang_xe == b.ma_hang_xe && b.so_cho == socho
                              select new Xe
                              {
                                  HangXe = a.ten_hang_xe,
                                  ThuongHieu = b.ten_thuong_hieu
                              }).ToList();

                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public List<Xe> TimXeTheoHieu(string hieu)
        {
            try
            {
                var result = (from a in db.hang_xe
                              from b in db.thuong_hieu
                              from c in db.chi_tiet_xe
                              where a.ma_hang_xe == b.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ten_hang_xe == hieu
                              select new Xe
                              {
                                  HangXe = a.ten_hang_xe,
                                  ThuongHieu = b.ten_thuong_hieu
                              }).ToList();

                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public List<Xe> TimXeTheoDichVu(string dichvu)
        {
            try
            {
                var result = (from a in db.hang_xe
                              from b in db.thuong_hieu
                              from c in db.bang_gia_trong_ngay
                              from d in db.dich_vu
                              from e in db.chi_tiet_xe
                              where a.ma_hang_xe == b.ma_hang_xe && b.ma_thuong_hieu == e.ma_thuong_hieu && e.bien_so == c.bien_so && c.ma_dich_vu == d.id_dich_vu && d.ten_dich_vu == dichvu
                              select new Xe
                              {
                                  HangXe = a.ten_hang_xe,
                                  ThuongHieu = b.ten_thuong_hieu
                              }).ToList();

                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BGTLTentity> listAllByRoute()
        {
            try
            {
                var result = (from a in db.bang_gia_theo_lo_trinh
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.tinh_trang_xe
                              from e in db.lo_trinh
                              from f in db.hang_xe
                              where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh
                              select new BGTLTentity
                              {
                                   HangXe = f.ten_hang_xe,
                                   Hinhanh = b.hinh_anh,
                                   MaLoTrinh = a.ma_lo_trinh,
                                   Ma_bien_so = a.ma_bien_so,
                                   Tinh_trang = d.tinh_trang,
                                   Ten_thuong_hieu = c.ten_thuong_hieu,
                                   So_cho = c.so_cho,
                                   Ten_lo_trinh = e.ten_lo_trinh,
                                   Mien = e.ma_mien,
                                   SoKM = e.so_km,
                                   Gia_tien = a.gia_tien,
                                   Chi_phi_vuot_km = a.chi_phi_vuot_km
                              }).ToList();

                return result;
               
            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BGTLTentity> listAllByRoute(int from,int size)
        {
            try
            {
                var result = (from a in db.bang_gia_theo_lo_trinh
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.tinh_trang_xe
                              from e in db.lo_trinh
                              from f in db.hang_xe
                              where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh
                              select new BGTLTentity
                              {
                                  HangXe = f.ten_hang_xe,
                                  Hinhanh = b.hinh_anh,
                                  MaLoTrinh = e.ma_lo_trinh,
                                  Ma_bien_so = a.ma_bien_so,
                                  Tinh_trang = d.tinh_trang,
                                  Ten_thuong_hieu = c.ten_thuong_hieu,
                                  So_cho = c.so_cho,
                                  Ten_lo_trinh = e.ten_lo_trinh,
                                  Mien = e.ma_mien,
                                  SoKM = e.so_km,
                                  Songay = e.so_ngay,
                                  Gia_tien = a.gia_tien,
                                  Chi_phi_vuot_km = a.chi_phi_vuot_km
                              }).OrderBy(t => t.Ma_bien_so).Skip(from).Take(size).ToList();

                return result;

            }
            catch (Exception)
            {
                return null;
            }
        }
        public int CountBGLTRecord()
        {
            var Linq_Results =
              (from a in db.bang_gia_theo_lo_trinh
               select a.ma_bien_so).Count();

            return Linq_Results;
        }
        public List<BGTLTentity> SearchBangGiaLT(int soCho)
        {
            try
            {
                if (soCho == 0)
                {
                    var result = (from a in db.bang_gia_theo_lo_trinh
                                  from b in db.chi_tiet_xe
                                  from c in db.thuong_hieu
                                  from d in db.tinh_trang_xe
                                  from e in db.lo_trinh
                                  from f in db.hang_xe
                                  where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh
                                  select new BGTLTentity
                                  {
                                      HangXe = f.ten_hang_xe,
                                      Hinhanh = b.hinh_anh,
                                      MaLoTrinh = e.ma_lo_trinh,
                                      Ma_bien_so = a.ma_bien_so,
                                      Tinh_trang = d.tinh_trang,
                                      Ten_thuong_hieu = c.ten_thuong_hieu,
                                      So_cho = c.so_cho,
                                      Ten_lo_trinh = e.ten_lo_trinh,
                                      Mien = e.ma_mien,
                                      SoKM = e.so_km,
                                      Songay = e.so_ngay,
                                      Gia_tien = a.gia_tien,
                                      Chi_phi_vuot_km = a.chi_phi_vuot_km
                                  }).ToList();

                    return result;
                }
                else
                {
                    var result = (from a in db.bang_gia_theo_lo_trinh
                                  from b in db.chi_tiet_xe
                                  from c in db.thuong_hieu
                                  from d in db.tinh_trang_xe
                                  from e in db.lo_trinh
                                  from f in db.hang_xe
                                  where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh && c.so_cho == soCho
                                  select new BGTLTentity
                                  {
                                      HangXe = f.ten_hang_xe,
                                      Hinhanh = b.hinh_anh,
                                      MaLoTrinh = e.ma_lo_trinh,
                                      Ma_bien_so = a.ma_bien_so,
                                      Tinh_trang = d.tinh_trang,
                                      Ten_thuong_hieu = c.ten_thuong_hieu,
                                      So_cho = c.so_cho,
                                      Ten_lo_trinh = e.ten_lo_trinh,
                                      Mien = e.ma_mien,
                                      SoKM = e.so_km,
                                      Songay = e.so_ngay,
                                      Gia_tien = a.gia_tien,
                                      Chi_phi_vuot_km = a.chi_phi_vuot_km
                                  }).ToList();

                    return result;
                }

            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BGTLTentity> SearchBangGiaLTByTenLT(string tenLT, int soCho)
        {
            try
            {
                if (soCho == 0)
                {
                    var result = (from a in db.bang_gia_theo_lo_trinh
                                  from b in db.chi_tiet_xe
                                  from c in db.thuong_hieu
                                  from d in db.tinh_trang_xe
                                  from e in db.lo_trinh
                                  from f in db.hang_xe
                                  where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh && e.ten_lo_trinh.Contains(tenLT)
                                  select new BGTLTentity
                                  {
                                      HangXe = f.ten_hang_xe,
                                      Hinhanh = b.hinh_anh,
                                      MaLoTrinh = e.ma_lo_trinh,
                                      Ma_bien_so = a.ma_bien_so,
                                      Tinh_trang = d.tinh_trang,
                                      Ten_thuong_hieu = c.ten_thuong_hieu,
                                      So_cho = c.so_cho,
                                      Ten_lo_trinh = e.ten_lo_trinh,
                                      Mien = e.ma_mien,
                                      SoKM = e.so_km,
                                      Songay = e.so_ngay,
                                      Gia_tien = a.gia_tien,
                                      Chi_phi_vuot_km = a.chi_phi_vuot_km
                                  }).ToList();
                    return result;
                }
                else
                {
                    var result = (from a in db.bang_gia_theo_lo_trinh
                                  from b in db.chi_tiet_xe
                                  from c in db.thuong_hieu
                                  from d in db.tinh_trang_xe
                                  from e in db.lo_trinh
                                  from f in db.hang_xe
                                  where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh && c.so_cho == soCho
                                  select new BGTLTentity
                                  {
                                      HangXe = f.ten_hang_xe,
                                      Hinhanh = b.hinh_anh,
                                      MaLoTrinh = e.ma_lo_trinh,
                                      Ma_bien_so = a.ma_bien_so,
                                      Tinh_trang = d.tinh_trang,
                                      Ten_thuong_hieu = c.ten_thuong_hieu,
                                      So_cho = c.so_cho,
                                      Ten_lo_trinh = e.ten_lo_trinh,
                                      Mien = e.ma_mien,
                                      SoKM = e.so_km,
                                      Songay = e.so_ngay,
                                      Gia_tien = a.gia_tien,
                                      Chi_phi_vuot_km = a.chi_phi_vuot_km
                                  }).ToList();
                    return result;
                }

            }
            catch (Exception)
            {
                return null;
            }
        }
        public BGTLTentity listAllByIDCar(string IdCar)
        {
            try
            {
                var result = (from a in db.bang_gia_theo_lo_trinh
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.tinh_trang_xe
                              from e in db.lo_trinh
                              from f in db.miens
                              from g in db.hang_xe
                              where a.ma_bien_so == b.bien_so && c.ma_hang_xe == g.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh && a.ma_bien_so == IdCar
                              select new BGTLTentity
                              {
                                  HangXe = g.ten_hang_xe,
                                  Hinhanh = b.hinh_anh,
                                  MaLoTrinh = a.ma_lo_trinh,
                                  Ma_bien_so = a.ma_bien_so,
                                  Tinh_trang = d.tinh_trang,
                                  Ten_thuong_hieu = c.ten_thuong_hieu,
                                  So_cho = c.so_cho,
                                  Ten_lo_trinh = e.ten_lo_trinh,
                                  Mien = e.ma_mien,
                                  SoKM = e.so_km,
                                  Songay = e.so_ngay,
                                  Gia_tien = a.gia_tien,
                                  Chi_phi_vuot_km = a.chi_phi_vuot_km
                              }).ToList();

                return result[0];

            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BGTLTentity> listAllByIDHangXe(string hangxe)
        {
            try
            {
                 var result = (from a in db.bang_gia_theo_lo_trinh
                                  from b in db.chi_tiet_xe
                                  from c in db.thuong_hieu
                                  from d in db.tinh_trang_xe
                                  from e in db.lo_trinh
                                  from f in db.hang_xe
                               where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh && c.ma_hang_xe == hangxe
                                  select new BGTLTentity
                                  {
                                      HangXe = f.ten_hang_xe,
                                      Hinhanh = b.hinh_anh,
                                      MaLoTrinh = e.ma_lo_trinh,
                                      Ma_bien_so = a.ma_bien_so,
                                      Tinh_trang = d.tinh_trang,
                                      Ten_thuong_hieu = c.ten_thuong_hieu,
                                      So_cho = c.so_cho,
                                      Ten_lo_trinh = e.ten_lo_trinh,
                                      Mien = e.ma_mien,
                                      SoKM = e.so_km,
                                      Songay = e.so_ngay,
                                      Gia_tien = a.gia_tien,
                                      Chi_phi_vuot_km = a.chi_phi_vuot_km
                                  }).ToList();
                                      return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BGTLTentity> listAllByIDHangXe(string hangxe,int from,int size)
        {
            try
            {
                var result = (from a in db.bang_gia_theo_lo_trinh
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.tinh_trang_xe
                              from e in db.lo_trinh
                              from f in db.hang_xe
                              where a.ma_bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh && c.ma_hang_xe == hangxe
                              select new BGTLTentity
                              {
                                  HangXe = f.ten_hang_xe,
                                  Hinhanh = b.hinh_anh,
                                  MaLoTrinh = e.ma_lo_trinh,
                                  Ma_bien_so = a.ma_bien_so,
                                  Tinh_trang = d.tinh_trang,
                                  Ten_thuong_hieu = c.ten_thuong_hieu,
                                  So_cho = c.so_cho,
                                  Ten_lo_trinh = e.ten_lo_trinh,
                                  Mien = e.ma_mien,
                                  SoKM = e.so_km,
                                  Songay = e.so_ngay,
                                  Gia_tien = a.gia_tien,
                                  Chi_phi_vuot_km = a.chi_phi_vuot_km
                              }).OrderBy(t => t.Ma_bien_so).Skip(from).Take(size).ToList();
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public int CountAllByIDHangXe(string hangxe)
        {
            try
            {
                var result = (from a in db.bang_gia_theo_lo_trinh
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              where a.ma_bien_so == b.bien_so && b.ma_thuong_hieu == c.ma_thuong_hieu && c.ma_hang_xe == hangxe
                              select new BGTLTentity
                              {
                              }).Count();
                return result;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        public List<BGTLTentity> listAllByRouteID(string id)
        {
            try
            {
                var result = (from a in db.bang_gia_theo_lo_trinh
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.tinh_trang_xe
                              from e in db.lo_trinh
                              where a.ma_bien_so == b.bien_so && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_lo_trinh == e.ma_lo_trinh && e.ma_mien == id
                              select new BGTLTentity
                              {
                                  Hinhanh = b.hinh_anh,
                                  Ma_bien_so = a.ma_bien_so,
                                  Tinh_trang = d.tinh_trang,
                                  Ten_thuong_hieu = c.ten_thuong_hieu,
                                  So_cho = c.so_cho,
                                  Ten_lo_trinh = e.ten_lo_trinh,
                                  Gia_tien = a.gia_tien,
                                  Chi_phi_vuot_km = a.chi_phi_vuot_km
                              }).ToList();

                return result;

            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BGTNentity> listAllByDay()
        {
            try
            {
                var result = (from a in db.bang_gia_trong_ngay
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.tinh_trang_xe
                              from e in db.dich_vu
                              from f in db.miens
                              where a.bien_so == b.bien_so && b.ma_thuong_hieu == c.ma_thuong_hieu && b.ma_tinh_trang_xe == d.ma_tinh_trang && a.ma_dich_vu == e.id_dich_vu && a.ma_mien == f.ma_mien
                              select new BGTNentity
                              {
                                  Bien_so = a.bien_so,
                                  Tinh_trang = d.tinh_trang,
                                  Ten_thuong_hieu = c.ten_thuong_hieu,
                                  So_cho = c.so_cho,
                                  Ten_dich_vu = e.ten_dich_vu,
                                  Ten_mien = f.ten_mien,
                                  Gia_tien = a.gia_tien,
                                  Gia_ngoai_gio  = a.gia_ngoai_gio,
                                  Gia_trang_tri = a.gia_trang_tri,
                                  Chi_phi_tai_xe = a.chi_phi_tai_xe,
                                  Phi_luu_dem = a.phi_luu_dem
                              }).ToList();

                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
    }
}
